package com.jcgyl.export.util;

import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Value;
import org.sql2o.Connection;
import org.sql2o.Query;
import org.sql2o.Sql2o;

public class MyDao {
	public static String server;
	public static String user;
	public static String password;
	private static Sql2o sql2o;

	private MyDao() {

	}

	public static Sql2o getInstance(String _server, String _user, String _password) {
		if (sql2o == null) {
			sql2o = new Sql2o(_server, _user, _password);
		}
		return sql2o;
	}

	public static Sql2o getInstance() {
		if (sql2o == null) {
			sql2o = new Sql2o(server, user, password);
		}
		return sql2o;
	}

	public static String getServer() {
		return server;
	}

	@Value("${spring.datasource.url}")
	public void setServer(String server) {
		MyDao.server = server;
	}

	public static String getUser() {
		return user;
	}

	public void setUser(String user) {
		MyDao.user = user;
	}

	public static String getPassword() {
		return password;
	}

	@Value("${spring.datasource.password}")
	public void setPassword(String password) {
		MyDao.password = password;
	}

	public static void insertABunchOfRows(Map entityColumns, List<Map> entityValues, String tableName) {
		StringBuffer columns = new StringBuffer();
		StringBuffer values = new StringBuffer();

		Iterator entries = entityColumns.entrySet().iterator();
		while (entries.hasNext()) {
			Map.Entry entry = (Map.Entry) entries.next();
			Map innerMap = (Map) entry.getValue();
			String key = (String) innerMap.get("code");
			columns.append(key);
			columns.append(',');
			values.append(':');
			values.append(key);
			values.append(',');
		}
		// 导入前先删除

		String sql = String.format("INSERT INTO %s (%s) VALUES(%s) ", tableName,
				columns.substring(0, columns.length() - 1), values.substring(0, values.length() - 1));
		System.out.println(sql);
		try (Connection con = sql2o.beginTransaction()) {

			Query query = con.createQuery(sql);
			int index = 1;
			boolean need_execute = true;
			for (Map map : entityValues) {
				need_execute = true;
				String express_number = map.get("express_number").toString();
				String delSql = String.format("DELETE FROM %s where express_number ='%s' ", tableName, express_number);
				Query delCommand = con.createQuery(delSql).addToBatch();
				delCommand.executeBatch();
				entries = map.entrySet().iterator();
				while (entries.hasNext()) {
					Map.Entry entry = (Map.Entry) entries.next();
					String key = (String) entry.getKey();
					Object object = entry.getValue();
					query.addParameter(key, object);
				}
				query.addToBatch();
				if ((index % 500) == 0) {
					query.executeBatch(); // executes entire batch
					System.out.println("批量保存中...");
					query = con.createQuery(sql);
					need_execute = false;
				}
				index++;
			}
			// query.setMaxBatchRecords(200);
			if (need_execute) {
				query.executeBatch(); // executes entire batch
			}
			con.commit(); // remember to call commit(), else sql2o will automatically rollback.
			con.close();
			System.out.println("批量保存已完成...");
		}
	}
}
